﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
namespace DAL
{
    /// <summary>
    /// 进货管理相关方法
    /// </summary>
    public class InproService
    {
        #region 1 查询指定商品所有的货品信息
        public List<sp_Product> GetProListByGoodsId(int GoodId)
        {
            string sql = "select * from sp_Product where Goods_id=" + GoodId;
            return DapperTools.Query<sp_Product>(sql);
        }
        #endregion

        #region 2 增加本次进货方法
        public bool AddInProduct(AddInProduct product)
        {
            bool f = false;
            try
            {
                //1 增加主表
                string inproSQL = "insert into [InProTab] values(@InProBH, @InproZDBH, @ProJJZG, @ProJJSL, @JHQTFY, @JHRQ, @bz, @GoodId, @GoodName)";
                DapperTools.Add(inproSQL, product.InProObject);//增加主表
                                                               //2 增加从表多条
                string InproListSQL = " insert into [InPorInfoTab] values(@InProZDBH, @ProID, @GoodId, @GoodName, @GoodProName, " +
                    "@GoodAttr, @ProJG, @ProSL, @ProPTJG, @BZ, @XJ)";
                DapperTools.Add(InproListSQL, product.InProInfoTab.Where(p => p.ProSL != 0));//用框架简单传入集合就OK

                //3 修改该库品在本次进价的成本价 //主要是把其他费用去平均 (可以按数量来平均，或 按货品价值来平均)
                //该示范使用数量来平均
                decimal add_fy = (decimal)product.InProObject.JHQTFY * (decimal)1.0 / product.InProObject.ProJJSL;
                //批量修改 : 本次查询出的货品列表,
                string selAllPro = " select * from [InPorInfoTab] where InProZDBH='" + product.InProObject.InProZDBH + "'";
                List<InProInfoTab> proAddList = DapperTools.Query<InProInfoTab>(selAllPro);
                foreach (var apro in proAddList)
                {
                    string updatesqlPro = "update [InPorInfoTab] set [ProPTJG]=[ProJG]+" + add_fy + " where id=" + apro.ID;
                    DapperTools.UpdateSQL(updatesqlPro);
                }
                //4 修改该货品库存   //5 修改该货品在仓库的成本价
                //先[再查询一次]
                string selAllPro2 = " select * from [InPorInfoTab] where InProZDBH='" + product.InProObject.InProZDBH + "'";
                List<InProInfoTab> proAddList2 = DapperTools.Query<InProInfoTab>(selAllPro2);
                foreach (var apro in proAddList2)
                {
                    //查询该货品在仓库中的编号来执行修改操作
                    string sql_Pro = "select * from [sp_Product] where ID=" + apro.ProID;//产品id查询
                    sp_Product updateProduct = DapperTools.Query<sp_Product>(sql_Pro).First();//只有一条，ID查询,集合取下标，或first
                                                                                              //计算成本价:计算算法，(库存数量*库存价+进货成本价*进货数量)/ ( 库存数量+进货数量)
                    decimal ? zhcbj = (updateProduct.Good_SKU_KC * updateProduct.Good_SKU_KCPrice
                        + apro.ProSL * apro.ProPTJG) / (updateProduct.Good_SKU_KC + apro.ProSL);
                    //数量:计算方法，  库存数量+进货数量
                    int ? asum = updateProduct.Good_SKU_KC + apro.ProSL;
                    //修改产品的库存数据 [条件为货品id]
                    string UpdateProductSql = "update [sp_Product] set [Good_SKU_KC]=" + asum + " ,[Good_SKU_KCPrice]="
                        + zhcbj + " where id= " + apro.ProID;
                    //执行修改操作
                    DapperTools.UpdateSQL(UpdateProductSql);
                }

                f = true;  //说明执行成功

            }
            catch (Exception ex)
            {
                throw ex;
            }


            return f;

        }
        #endregion

        #region 3查询所有进货信息
        /// <summary>
        /// 
        /// </summary>
        /// <param name="key">查询关键字</param>
        /// <param name="currentPage">当前页数</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="JxZs">该查询的记录总数</param>
        /// <returns></returns>
        public List<InProTab> GetAll(string key, ref int JxZs, int currentPage = 1, int pagesize = 10)
        {
            //用语句访问
            string sql = " select * from  [InProTab]  where  1=1  ";
            string sql2 = " select count(*) from  [InProTab]  where  1=1 ";
            //通用分页语句
            /*
             * select top 10 * from  [InProTab] where 1=1  and  id not in 
            (
            select top 1 id from InProTab  where 1=1  order by id desc
            )
            order by id desc 
             */
            int n = (currentPage - 1) * pagesize;
            string sql3 = " select top "+pagesize+"*  from  [InProTab]  where  1=1  ";
            string sql4 = " select  top "+n+" id  from  [InProTab]  where  1=1 ";
            if (!string.IsNullOrEmpty(key))
            {
                sql += "  and GoodName like '%" + key + "%' ";
                sql2 += "  and GoodName like '%" + key + "%' ";

                sql3 += " and GoodName like '%" + key + "%' ";
                sql4 += " and GoodName like '%" + key + "%' ";
            }
            //通过查询获取记录总数
            JxZs = DapperTools.GetZhi<int>(sql2);

            //排除行数
            //sql 2012自带分页函数
            // int n = (currentPage - 1) * pagesize;
            //  sql += " order by Id desc offset " + n + " rows fetch next " + pagesize + " rows only ";
            //执行
            // List<InProTab> proAlllist = DapperTools.Query<InProTab>(sql);
            //调用通过分页吧
            string sql_r = sql3 + " and id not in ( " + sql4 + " order by id desc ) order by id desc ";
            List<InProTab> proAlllist = DapperTools.Query<InProTab>(sql_r);
            return proAlllist;
        }
        #endregion

        #region 4 查询一次进货的详细情况
        public List<InProInfoTab> GetInProInfoByInProZDBH(string Inzdbh)
        {
            string sql = "select * from [InPorInfoTab]  where [InProZDBH]='" + Inzdbh + "'";
            List<InProInfoTab> infoList = DapperTools.Query<InProInfoTab>(sql);
            return infoList;
        }
        #endregion
    }
}
